﻿using Md.Api.Entity.ModelDB;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 系统部门仓储
/// </summary>
public class SysDeptRep : BaseRep<SysDeptModel>
{
    /// <summary>
    /// 通过部门编号获取系统部门信息
    /// </summary>
    /// <param name="deptId">部门编号</param>
    /// <returns>SysDeptModel</returns>
    public SysDeptModel GetSysDeptByDeptId(long deptId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select `dept_id`
                  ,`parent_id`
                  ,`ancestors`
                  ,`dept_name`
                  ,`order_num`
                  ,`leader`
                  ,`phone`
                  ,`email`
                  ,`status`
                  ,`del_flag`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
            from `sys_dept`
            where 1 = 1 and dept_id = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysDeptModel>(sqlStr, deptId);
    }
    /// <summary>
    /// 获取系统部门列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>IEnumerable<SysDeptModel></returns>
    public IEnumerable<SysDeptModel> GetSysDeptList(SysDeptModel model, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.DeptId > 0)
        {
            sqlWhere += " and dept_id = @0 ";
        }
        if (model.ParentId > -1)
        {
            sqlWhere += " and parent_id = @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Ancestors))
        {
            sqlWhere += " and find_in_set(@2, ancestors) ";
        }
        if (!string.IsNullOrWhiteSpace(model.DeptName))
        {
            sqlWhere += " and dept_name like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Leader))
        {
            sqlWhere += " and leader like @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Phone))
        {
            sqlWhere += " and phone like @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sqlWhere += " and email like @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @10 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @11 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @12 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `dept_id`
                  ,`parent_id`
                  ,`ancestors`
                  ,`dept_name`
                  ,`order_num`
                  ,`leader`
                  ,`phone`
                  ,`email`
                  ,`status`
                  ,`del_flag`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
            from `sys_dept`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<SysDeptModel>(
            sqlStr,
            model.DeptId,
            model.ParentId,
            model.Ancestors,
            string.Format(@"%{0}%", model.DeptName),
            string.Format(@"%{0}%", model.Leader),
            string.Format(@"%{0}%", model.Phone),
            string.Format(@"%{0}%", model.Email),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 分页获取系统部门列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>Page<SysDeptModel></returns>
    public Page<SysDeptModel> GetSysDeptPageList(SysDeptModel model, int pageIndex, int pageSize, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.DeptId > 0)
        {
            sqlWhere += " and dept_id = @0 ";
        }
        if (model.ParentId > -1)
        {
            sqlWhere += " and parent_id = @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Ancestors))
        {
            sqlWhere += " and find_in_set(@2, ancestors) ";
        }
        if (!string.IsNullOrWhiteSpace(model.DeptName))
        {
            sqlWhere += " and dept_name like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Leader))
        {
            sqlWhere += " and leader like @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Phone))
        {
            sqlWhere += " and phone like @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sqlWhere += " and email like @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @10 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @11 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @12 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `dept_id`
                  ,`parent_id`
                  ,`ancestors`
                  ,`dept_name`
                  ,`order_num`
                  ,`leader`
                  ,`phone`
                  ,`email`
                  ,`status`
                  ,`del_flag`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
            from `sys_dept`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<SysDeptModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.DeptId,
            model.ParentId,
            model.Ancestors,
            string.Format(@"%{0}%", model.DeptName),
            string.Format(@"%{0}%", model.Leader),
            string.Format(@"%{0}%", model.Phone),
            string.Format(@"%{0}%", model.Email),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 通过部门编号更新系统部门信息
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>int</returns>
    public long UpdateSysDeptByDeptId(SysDeptModel model)
    {
        var sql = new Sql();

        sql.Append(" update `sys_dept` set ");

        #region ===set===
        if (model.ParentId > -1)
        {
            sql.Append(" parent_id = @0, ", model.ParentId);
        }

        if (!string.IsNullOrWhiteSpace(model.Ancestors))
        {
            sql.Append(" ancestors = @0, ", model.Ancestors);
        }

        if (!string.IsNullOrWhiteSpace(model.DeptName))
        {
            sql.Append(" dept_name = @0, ", model.DeptName);
        }

        if (model.OrderNum > -1)
        {
            sql.Append(" order_num = @0, ", model.OrderNum);
        }

        if (!string.IsNullOrWhiteSpace(model.Leader))
        {
            sql.Append(" leader = @0, ", model.Leader);
        }

        if (!string.IsNullOrWhiteSpace(model.Phone))
        {
            sql.Append(" phone = @0, ", model.Phone);
        }

        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sql.Append(" email = @0, ", model.Email);
        }

        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sql.Append(" status = @0, ", model.Status);
        }

        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sql.Append(" update_by = @0, ", model.UpdateBy);
        }

        sql.Append(" update_time = sysdate() ");
        #endregion

        sql.Append(" where 1 = 1 and del_flag = @0 and dept_id = @1 ", model.DelFlag, model.DeptId);

        return MdTrainingEducationDB.GetInstance().Execute(sql);
    }
}